﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MiniKsiegowosc.Wrk.Ksiegi.KPIR;
using System.Data;
using ProgNetComponents.Data.SqlCe;
using ProgNetComponents.Data;
using MiniKsiegowosc.Wrk.Ksiegi.VatS;
using ProgNetDxComponents.ExtenderProviders.Browsers;
using MiniKsiegowosc.Wrk.Ksiegi.VatZ;
using ProgNet.Constants;
using MiniKsiegowosc.Ustawienia.VatZ;
using MiniKsiegowosc.Ustawienia.VatS;

namespace MiniKsiegowosc.Wrk
{
    public class WrkTools
    {
        public static bool IsRokValid(int rok)
        {
            return BrowserCache.IsValueValid(BrowserNames.RokPodatkowy, rok);
        }
        public static bool RokHasData(int rok)
        {
            List<string> list = new List<string>(){
               "SELECT COUNT (1) FROM WRK_KPIR where WRK_KPIR_ROK = "+ rok,
               "SELECT COUNT (1) FROM WRK_VATS where WRK_VATS_ROK = "+rok,
               "SELECT COUNT (1) FROM WRK_VATZ where WRK_VATZ_ROK = "+rok
           };
            foreach (string s in list)
            {
                if (manager.ExecuteScalar(s).AsInt() > 0)
                    return true;
            }
            return false;
        }

        private static SqlCeDataManager _manager = null;
        private static SqlCeDataManager manager
        {
            get
            {
                if (_manager == null)
                {
                    _manager = new SqlCeDataManager();
                    PrepareManager();
                }
                return _manager;
            }
        }
        private static void PrepareManager()
        {
            DataTable dt = new DataTable("STP_KPIR");
            manager.AddTableDef(dt, "SELECT * FROM STP_KPIR WHERE STP_KPIR_ID = @ID");
            dt = new DataTable("STP_KPIR_KWOTY");
            manager.AddTableDef(dt, "SELECT * FROM STP_KPIR_KWOTY WHERE STP_KPIRK_FAKTURA = @ID");
            dt = new DataTable("STP_VATS_KWOTY");
            manager.AddTableDef(dt, "SELECT * FROM STP_VATS_KWOTY WHERE STP_VATSK_FAKTURA = @ID");
            dt = new DataTable("STP_VATS");
            manager.AddTableDef(dt, "SELECT * FROM STP_VATS WHERE STP_VATS_ID = @ID");
            dt = new DataTable("STP_VATZ_KWOTY");
            manager.AddTableDef(dt, "SELECT * FROM STP_VATZ_KWOTY WHERE STP_VATZK_FAKTURA = @ID");
            dt = new DataTable("STP_VATZ");
            manager.AddTableDef(dt, "SELECT * FROM STP_VATZ WHERE STP_VATZ_ID = @ID");
        }
        #region KPIR
        public static DataRow GetPredefKpir(int id)
        {
            DataTable dt = new DataTable("STP_KPIR");
            manager.FillTable(dt, new ObjectKeyValuePair("@ID", id));
            return dt.SelectFirstRow();
        }
        public static DataRow[] GetKwotyForKpir(int id)
        {
            DataTable dt = new DataTable("STP_KPIR_KWOTY");
            manager.FillTable(dt, new ObjectKeyValuePair("@ID", id));
            return dt.Select();
        }
        public static void AddKpirFromPredef(WrkKpirDataSet ds, int PredefId, DataRow currentRow)
        {
            DataRow r = GetPredefKpir(PredefId);
            if (r != null)
            {
                WrkKpirDataSet.WRK_KPIRRow newFaktura = ds.WRK_KPIR.NewWRK_KPIRRow();
                newFaktura["WRK_KPIR_KONTRACHENT"] = r["STP_KPIR_KONTRACHENT"];
                newFaktura["WRK_KPIR_UWAGI"] = r["STP_KPIR_UWAGI"];
                newFaktura["WRK_KPIR_OPIS"] = r["STP_KPIR_OPIS"];
                newFaktura.WRK_KPIR_DATA = DateTime.Today;
                newFaktura.MIESIAC = DateTime.Today.Month;
                newFaktura.WRK_KPIR_ROK = DateTime.Today.Year;
                int id = newFaktura.WRK_KPIR_ID;
                if (currentRow != null)
                {
                    id = currentRow["WRK_KPIR_ID"].AsInt();
                    foreach (DataColumn c in ds.WRK_KPIR.Columns)
                    {
                        if (c != ds.WRK_KPIR.WRK_KPIR_IDColumn &&
                            c != ds.WRK_KPIR.WRK_KPIR_NUMER_DOWODU_KSIEGOWEGOColumn)
                        {
                            currentRow[c.ColumnName] = newFaktura[c.ColumnName];
                        }
                    }
                    currentRow.DeleteChildRows();
                }
                else
                {
                    ds.WRK_KPIR.AddWRK_KPIRRow(newFaktura);
                }

                DataRow[] kwoty = GetKwotyForKpir(PredefId);
                WrkKpirDataSet.WRK_KPIR_KWOTYRow newKwota = null;
                foreach (DataRow kw in kwoty)
                {
                    newKwota = ds.WRK_KPIR_KWOTY.NewWRK_KPIR_KWOTYRow();
                    newKwota["WRK_KPIRK_FAKTURA"] = id;
                    newKwota["WRK_KPIRK_KWOTA_NETTO"] = kw["STP_KPIRK_KWOTA_NETTO"];
                    newKwota["WRK_KPIRK_TYP"] = kw["STP_KPIRK_TYP"];
                    ds.WRK_KPIR_KWOTY.AddWRK_KPIR_KWOTYRow(newKwota);
                }
            }
        }
        #endregion

        #region VatS
        public static DataRow[] GetKwotyForVatS(int id)
        {
            DataTable dt = new DataTable("STP_VATS_KWOTY");
            manager.FillTable(dt, new ObjectKeyValuePair("@ID", id));
            return dt.Select();
        }
        public static DataRow GetPredefVatS(int id)
        {
            DataTable dt = new DataTable("STP_VATS");
            manager.FillTable(dt, new ObjectKeyValuePair("@ID", id));
            return dt.SelectFirstRow();
        }
        public static void AddVatSFromPredef(WrkVatSDataSet ds, int PredefId, DataRow currentRow)
        {
            DataRow r = GetPredefVatS(PredefId);
            if (r != null)
            {

                WrkVatSDataSet.WRK_VATSRow newFaktura = ds.WRK_VATS.NewWRK_VATSRow();
                newFaktura["WRK_VATS_NABYWCA"] = r["STP_VATS_NABYWCA"];
                newFaktura["WRK_VATS_UWAGI"] = r["STP_VATS_UWAGI"];
                newFaktura["WRK_VATS_OPIS"] = r["STP_VATS_OPIS"];
                newFaktura.WRK_VATS_DATA_SPRZEDAZY = DateTime.Today;
                newFaktura.WRK_VATS_DATA_WYSTAWIENIA = DateTime.Today;
                newFaktura.WRK_VATS_ROK = DateTime.Today.Year;
                newFaktura.MIESIAC = DateTime.Today.Month;
                int id = newFaktura.WRK_VATS_ID;
                if (currentRow != null)
                {
                    id = currentRow["WRK_VATS_ID"].AsInt();
                    foreach (DataColumn c in ds.WRK_VATS.Columns)
                    {
                        if (c != ds.WRK_VATS.WRK_VATS_IDColumn &&
                            c != ds.WRK_VATS.WRK_VATS_NUMERColumn)
                        {
                            currentRow[c.ColumnName] = newFaktura[c.ColumnName];
                        }
                    }
                    currentRow.DeleteChildRows();
                }
                else
                {
                    ds.WRK_VATS.AddWRK_VATSRow(newFaktura);
                }

                DataRow[] kwoty = GetKwotyForVatS(PredefId);
                WrkVatSDataSet.WRK_VATS_KWOTYRow newKwota = null;
                foreach (DataRow kw in kwoty)
                {
                    newKwota = ds.WRK_VATS_KWOTY.NewWRK_VATS_KWOTYRow();
                    newKwota["WRK_VATSK_FAKTURA"] = id;
                    newKwota["WRK_VATSK_KWOTA_NETTO"] = kw["STP_VATSK_KWOTA_NETTO"];
                    newKwota["WRK_VATSK_TYP"] = kw["STP_VATSK_TYP"];
                    newKwota["WRK_VATSK_TYP_KPIR"] = kw["STP_VATSK_TYP_KPIR"];
                    newKwota["WRK_VATSK_ILOSC"] = kw["STP_VATSK_ILOSC"];
                    newKwota["WRK_VATSK_NAZWA"] = kw["STP_VATSK_NAZWA"];
                    ds.WRK_VATS_KWOTY.AddWRK_VATS_KWOTYRow(newKwota);
                }
            }
        }
        public static bool SaveVatSAsPredef(DataRow faktura, string name, ref string msg)
        {
            bool result = false;

            VatSDataSet setupDs = new VatSDataSet();
            DataRow newFaktura = null;
            DataRow newKwota = null;
            DataRow[] kwoty = null;

            newFaktura = setupDs.STP_VATS.NewRow();
            newFaktura["STP_VATS_NAZWA"] = name;
            newFaktura["STP_VATS_NABYWCA"] = faktura["WRK_VATS_NABYWCA"];
            newFaktura["STP_VATS_UWAGI"] = faktura["WRK_VATS_UWAGI"];
            newFaktura["STP_VATS_OPIS"] = faktura["WRK_VATS_OPIS"];
            setupDs.STP_VATS.Rows.Add(newFaktura);

            kwoty = faktura.GetAllChildRows();
            foreach (DataRow kw in kwoty)
            {
                newKwota = setupDs.STP_VATS_KWOTY.NewRow();
                newKwota["STP_VATSK_FAKTURA"] = newFaktura["STP_VATS_ID"];
                newKwota["STP_VATSK_KWOTA_NETTO"] = kw["WRK_VATSK_KWOTA_NETTO"];
                newKwota["STP_VATSK_TYP"] = kw["WRK_VATSK_TYP"];
                newKwota["STP_VATSK_TYP_KPIR"] = kw["WRK_VATSK_TYP_KPIR"];
                setupDs.STP_VATS_KWOTY.Rows.Add(newKwota);
            }
            result = manager.UpdateDataSet(setupDs);
            if (result)
                BrowserCache.DataGrabber.SetLastUpdateTime(setupDs);

            if (!result && manager.LastException != null)
                if (!manager.GetAutoLogExceptions)
                manager.LastException.Log("SaveVatSAsPredef");

            return result;
        }
       
        public static bool AddVatSToKpir(WrkVatSDataSet ds, ProgNetComponents.Data.DataManager manager)
        {
            return AddVatSToKpir(ds, null, manager);
        }
        public static bool AddVatSToKpir(WrkVatSDataSet ds, DataRow[] fakturyRows, ProgNetComponents.Data.DataManager manager)
        {
            WrkKpirDataSet kpir = new WrkKpirDataSet();
            manager.AddTableDef(kpir.WRK_KPIR);
            manager.AddTableDef(kpir.WRK_KPIR_KWOTY);
            DataRow[] rows = null;
            if (fakturyRows != null)
                rows = fakturyRows;
            else
                rows = ds.WRK_VATS.Select("DODAJ_DO_KPIR = 1", null, DataViewRowState.CurrentRows);

            DataRow[] kwoty = null;
            WrkKpirDataSet.WRK_KPIRRow faktura = null;
            WrkKpirDataSet.WRK_KPIR_KWOTYRow kwota = null;
            foreach (DataRow r in rows)
            {
                faktura = kpir.WRK_KPIR.NewWRK_KPIRRow();
                faktura["WRK_KPIR_DATA"] = r["WRK_VATS_DATA_WYSTAWIENIA"];
                faktura["WRK_KPIR_NUMER_DOWODU_KSIEGOWEGO"] = r["WRK_VATS_NUMER"];
                faktura["WRK_KPIR_KONTRACHENT"] = r["WRK_VATS_NABYWCA"];
                faktura["WRK_KPIR_OPIS"] = r["WRK_VATS_OPIS"];
                faktura["WRK_KPIR_UWAGI"] = r["WRK_VATS_UWAGI"];
                //faktura["WRK_KPIR_ROK_PODATKOWY"] = r["WRK_VATS_ROK_PODATKOWY"];
                kpir.WRK_KPIR.AddWRK_KPIRRow(faktura);
                kwoty = ds.WRK_VATS_KWOTY.Select("WRK_VATSK_FAKTURA = " + r["WRK_VATS_ID"].AsString());

                foreach (DataRow kw in kwoty)
                {
                    kwota = kpir.WRK_KPIR_KWOTY.NewWRK_KPIR_KWOTYRow();
                    kwota["WRK_KPIRK_FAKTURA"] = faktura["WRK_KPIR_ID"];
                    kwota["WRK_KPIRK_KWOTA_NETTO"] = kw["WRK_VATSK_KWOTA_NETTO"].AsDecimal() * kw["WRK_VATSK_ILOSC"].AsDecimal();
                    kwota["WRK_KPIRK_TYP"] = kw["WRK_VATSK_TYP_KPIR"];
                    kpir.WRK_KPIR_KWOTY.Rows.Add(kwota);
                }
            }
            bool res = manager.UpdateDataSet(kpir);
            if (!res && manager.LastException != null)
                if (!manager.GetAutoLogExceptions)
                manager.LastException.Log("AddToKpir");
            return res;
        }
        #endregion

        #region VatZ
        public static DataRow[] GetKwotyForVatZ(int id)
        {
            DataTable dt = new DataTable("STP_VATZ_KWOTY");
            manager.FillTable(dt, new ObjectKeyValuePair("@ID", id));
            return dt.Select();
        }
        public static DataRow GetPredefVatZ(int id)
        {
            DataTable dt = new DataTable("STP_VATZ");
            manager.FillTable(dt, new ObjectKeyValuePair("@ID", id));
            return dt.SelectFirstRow();
        }
        public static void AddVatZFromPredef(WrkVatZDataSet ds, int PredefId, DataRow currentRow)
        {
            DataRow r = GetPredefVatZ(PredefId);
            if (r != null)
            {
                WrkVatZDataSet.WRK_VATZRow newFaktura = ds.WRK_VATZ.NewWRK_VATZRow();
                newFaktura["WRK_VATZ_SPRZEDAWCA"] = r["STP_VATZ_SPRZEDAWCA"];
                newFaktura["WRK_VATZ_UWAGI"] = r["STP_VATZ_UWAGI"];
                newFaktura["WRK_VATZ_OPIS"] = r["STP_VATZ_OPIS"];
                newFaktura["DODAJ_DO_KPIR"] = r["STP_VATZ_DODAJ_DO_KPIR"];
                newFaktura.WRK_VATZ_DATA_NABYCIA_PRAW = DateTime.Today;
                newFaktura.WRK_VATZ_DATA_WPLYWU = DateTime.Today;
                newFaktura.WRK_VATZ_DATA_WYSTAWIENIA = DateTime.Today;
                newFaktura.WRK_VATZ_ROK = DateTime.Today.Year;
                newFaktura.MIESIAC = DateTime.Today.Month;
                int id = newFaktura.WRK_VATZ_ID;
                if (currentRow != null)
                {
                    id = currentRow["WRK_VATZ_ID"].AsInt();
                    foreach (DataColumn c in ds.WRK_VATZ.Columns)
                    {
                        if (c != ds.WRK_VATZ.WRK_VATZ_IDColumn &&
                            c != ds.WRK_VATZ.WRK_VATZ_NUMERColumn)
                        {
                            currentRow[c.ColumnName] = newFaktura[c.ColumnName];
                        }
                    }
                    currentRow.DeleteChildRows();
                }
                else
                {
                    ds.WRK_VATZ.AddWRK_VATZRow(newFaktura);
                }

                DataRow[] kwoty = GetKwotyForVatZ(PredefId);
                WrkVatZDataSet.WRK_VATZ_KWOTYRow newKwota = null;
                foreach (DataRow kw in kwoty)
                {
                    newKwota = ds.WRK_VATZ_KWOTY.NewWRK_VATZ_KWOTYRow();
                    newKwota["WRK_VATZK_FAKTURA"] = id;
                    newKwota["WRK_VATZK_KWOTA_NETTO"] = kw["STP_VATZK_KWOTA_NETTO"];
                    newKwota["WRK_VATZK_TYP"] = kw["STP_VATZK_TYP"];
                    newKwota["WRK_VATZK_TYP_KPIR"] = kw["STP_VATZK_TYP_KPIR"];
                    newKwota["WRK_VATZK_STAWKA_VAT"] = kw["STP_VATZK_STAWKA_VAT"];
                    ds.WRK_VATZ_KWOTY.AddWRK_VATZ_KWOTYRow(newKwota);
                }
            }
        }
        public static bool SaveVatZAsPredef(DataRow faktura, string name, ref string msg)
        {
            bool result = false;

            VatZDataSet setupDs = new VatZDataSet();
            DataRow newFaktura = null;
            DataRow newKwota = null;
            DataRow[] kwoty = null;

            newFaktura = setupDs.STP_VATZ.NewRow();
            newFaktura["STP_VATZ_NAZWA"] = name;
            newFaktura["STP_VATZ_SPRZEDAWCA"] = faktura["WRK_VATZ_SPRZEDAWCA"];
            newFaktura["STP_VATZ_UWAGI"] = faktura["WRK_VATZ_UWAGI"];
            newFaktura["STP_VATZ_OPIS"] = faktura["WRK_VATZ_OPIS"];
            newFaktura["STP_VATZ_DODAJ_DO_KPIR"] = true;
            setupDs.STP_VATZ.Rows.Add(newFaktura);

            kwoty = faktura.GetAllChildRows(true);
            foreach (DataRow kw in kwoty)
            {
                newKwota = setupDs.STP_VATZ_KWOTY.NewRow();
                newKwota["STP_VATZK_FAKTURA"] = newFaktura["STP_VATZ_ID"];
                newKwota["STP_VATZK_KWOTA_NETTO"] = kw["WRK_VATZK_KWOTA_NETTO"];
                newKwota["STP_VATZK_STAWKA_VAT"] = kw["WRK_VATZK_STAWKA_VAT"];
                newKwota["STP_VATZK_TYP"] = kw["WRK_VATZK_TYP"];
                newKwota["STP_VATZK_TYP_KPIR"] = kw["WRK_VATZK_TYP_KPIR"];
                setupDs.STP_VATZ_KWOTY.Rows.Add(newKwota);
            }
            result = manager.UpdateDataSet(setupDs);
            if (result)
                BrowserCache.DataGrabber.SetLastUpdateTime(setupDs);

            if (!result && manager.LastException != null)
                if (!manager.GetAutoLogExceptions)
                manager.LastException.Log("SaveVatSAsPredef");
            return result;
        }
        
        public static bool AddVatZToKpir(WrkVatZDataSet ds, ProgNetComponents.Data.DataManager manager)
        {
            return AddVatZToKpir(ds, null, manager);
        }
        public static bool AddVatZToKpir(WrkVatZDataSet ds, DataRow[] fakturyRows, ProgNetComponents.Data.DataManager manager)
        {
            WrkKpirDataSet kpir = new WrkKpirDataSet();
            manager.AddTableDef(kpir.WRK_KPIR);
            manager.AddTableDef(kpir.WRK_KPIR_KWOTY);
            DataRow[] rows = null;
            if (fakturyRows != null)
                rows = fakturyRows;
            else
                rows = ds.WRK_VATZ.Select("DODAJ_DO_KPIR = 1", null, DataViewRowState.CurrentRows);

            DataRow[] kwoty = null;
            WrkKpirDataSet.WRK_KPIRRow faktura = null;
            WrkKpirDataSet.WRK_KPIR_KWOTYRow kwota = null;
            foreach (DataRow r in rows)
            {
                faktura = kpir.WRK_KPIR.NewWRK_KPIRRow();
                faktura["WRK_KPIR_DATA"] = r["WRK_VATZ_DATA_WYSTAWIENIA"];
                faktura["WRK_KPIR_NUMER_DOWODU_KSIEGOWEGO"] = r["WRK_VATZ_NUMER"];
                faktura["WRK_KPIR_KONTRACHENT"] = r["WRK_VATZ_SPRZEDAWCA"];
                faktura["WRK_KPIR_OPIS"] = r["WRK_VATZ_OPIS"];
                faktura["WRK_KPIR_UWAGI"] = r["WRK_VATZ_UWAGI"];
                //faktura["WRK_KPIR_ROK_PODATKOWY"] = r["WRK_VATS_ROK_PODATKOWY"];
                kpir.WRK_KPIR.AddWRK_KPIRRow(faktura);
                kwoty = ds.WRK_VATZ_KWOTY.Select("WRK_VATZK_FAKTURA = " + r["WRK_VATZ_ID"].AsString());

                foreach (DataRow kw in kwoty)
                {
                    kwota = kpir.WRK_KPIR_KWOTY.NewWRK_KPIR_KWOTYRow();
                    kwota["WRK_KPIRK_FAKTURA"] = faktura["WRK_KPIR_ID"];
                    kwota["WRK_KPIRK_KWOTA_NETTO"] = kw["WRK_VATZK_KWOTA_NETTO"].AsDecimal();
                    kwota["WRK_KPIRK_TYP"] = kw["WRK_VATZK_TYP_KPIR"];
                    kpir.WRK_KPIR_KWOTY.Rows.Add(kwota);
                }
            }
            bool res = manager.UpdateDataSet(kpir);
            if (!res && manager.LastException != null)
                if (!manager.GetAutoLogExceptions)
                manager.LastException.Log("AddToKpir");
            return res;
        }
        #endregion
    }
}
